#! /bin/bash
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
echo '========================================'
echo '==============循环导入==============='
echo '========================================'
hive  -S -e  "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--============拉链表==========--
-- 客户事实表
truncate table bxg_dwd.ft_customer_relationship_tmp;
insert overwrite table  bxg_dwd.ft_customer_relationship_tmp
select
id,
create_date_time,
update_date_time,
cast(year(create_date_time) as string) as year,
substr(create_date_time,1,7) as year_month,
cast((create_date_time) as string) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
customer_id,
first_id,
belonger,
belonger_name,
initial_belonger,
distribution_handler,
business_scrm_department_id,
last_visit_time,
next_visit_time,
case when origin_type='NETSERVICE' or origin_type='PRESIGNUP' then 'ONLINE'
     else 'OFFLINE' end as origin_type,
itcast_school_id,
itcast_subject_id,
intention_study_type,
anticipat_signup_date,
level,
creator,
current_creator,
creator_name,
origin_channel,
comment,
first_customer_clue_id,
last_customer_clue_id,
process_state,
process_time,
payment_state,
payment_time,
signup_state,
signup_time,
notice_state,
notice_time,
lock_state,
lock_time,
itcast_clazz_id,
itcast_clazz_time,
payment_url,
payment_url_time,
ems_student_id,
delete_reason,
deleter,
deleter_name,
delete_time,
course_id,
course_name,
delete_comment,
close_state,
close_time,
appeal_id,
tenant,
total_fee,
belonged,
belonged_time,
belonger_time,
transfer,
transfer_time,
follow_type,
transfer_bxg_oa_account,
transfer_bxg_belonger_name,
'9999-99-99' as end_date,
dt as start_date
from
bxg_ods.customer_relationship
where dt = '${TD_DATE}'
union all
select a.id,
       a.create_date_time,
       a.update_date_time,
       \`year\`,
       year_month,
       \`month\`,
       year_month_day,
       \`day\`,
       a.deleted,
       a.customer_id,
       a.first_id,
       a.belonger,
       a.belonger_name,
       a.initial_belonger,
       a.distribution_handler,
       a.business_scrm_department_id,
       a.last_visit_time,
       a.next_visit_time,
       a.origin_type,
       a.itcast_school_id,
       a.itcast_subject_id,
       a.intention_study_type,
       a.anticipat_signup_date,
       a.level,
       a.creator,
       a.current_creator,
       a.creator_name,
       a.origin_channel,
       a.comment,
       a.first_customer_clue_id,
       a.last_customer_clue_id,
       a.process_state,
       a.process_time,
       a.payment_state,
       a.payment_time,
       a.signup_state,
       a.signup_time,
       a.notice_state,
       a.notice_time,
       a.lock_state,
       a.lock_time,
       a.itcast_clazz_id,
       a.itcast_clazz_time,
       a.payment_url,
       a.payment_url_time,
       a.ems_student_id,
       a.delete_reason,
       a.deleter,
       a.deleter_name,
       a.delete_time,
       a.course_id,
       a.course_name,
       a.delete_comment,
       a.close_state,
       a.close_time,
       a.appeal_id,
       a.tenant,
       a.total_fee,
       a.belonged,
       a.belonged_time,
       a.belonger_time,
       a.transfer,
       a.transfer_time,
       a.follow_type,
       a.transfer_bxg_oa_account,
       a.transfer_bxg_belonger_name,
       if(b.id is null and a.end_date <> '9999-99-99',a.end_date,date_sub(b.dt,1)) as end_date,
       a.start_date
from
bxg_dwd.ft_customer_relationship a
left join
(select * from bxg_ods.customer_relationship where dt='${TD_DATE}') b
on a.id = b.id;
-- 临时表插入到客户意向表
insert overwrite table bxg_dwd.ft_customer_relationship partition(start_date)
select
*
from bxg_dwd.ft_customer_relationship_tmp;

-- 客户线索表
truncate table bxg_dwd.ft_customer_clue_tmp;
insert overwrite table bxg_dwd.ft_customer_clue_tmp
select id,
       create_date_time,
       update_date_time,
       cast(year(create_date_time) as string) as year,
       substr(create_date_time,1,7) as year_month,
       cast((create_date_time) as string) as month,
       substr(create_date_time,1,10) as year_month_day,
       substr(create_date_time,1,19)  as day,
       deleted,
       customer_id,
       customer_relationship_id,
       session_id,
       sid,
       status,
       \`user\`,
       create_time,
       platform,
       s_name,
       seo_source,
       seo_keywords,
       ip,
       referrer,
       from_url,
       landing_page_url,
       url_title,
       to_peer,
       manual_time,
       begin_time,
       reply_msg_count,
       total_msg_count,
       msg_count,
       comment,
       finish_reason,
       finish_user,
       end_time,
       platform_description,
       browser_name,
       os_info,
       area,
       country,
       province,
       city,
       creator,
       name,
       idcard,
       phone,
       itcast_school_id,
       itcast_school,
       itcast_subject_id,
       itcast_subject,
       wechat,
       qq,
       email,
       gender,
       level,
       case when origin_type='NETSERVICE' or origin_type='PRESIGNUP' then 'ONLINE'
			else 'OFFLINE' end as origin_type,
       information_way,
       working_years,
       technical_directions,
       customer_state,
       valid,
       anticipat_signup_date,
       clue_state,
       scrm_department_id,
       superior_url,
       superior_source,
       landing_url,
       landing_source,
       info_url,
       info_source,
       origin_channel,
       course_id,
       course_name,
       zhuge_session_id,
       is_repeat,
       tenant,
       activity_id,
       activity_name,
       follow_type,
       shunt_mode_id,
       shunt_employee_group_id,
       '9999-99-99' as end_date,
       dt as start_date
from bxg_ods.customer_clue
where dt='${TD_DATE}'
union all
select a.id,
       a.create_date_time,
       a.update_date_time,
       \`year\`,
       year_month,
       \`month\`,
       year_month_day,
       \`day\`,
       a.deleted,
       a.customer_id,
       a.customer_relationship_id,
       a.session_id,
       a.sid,
       a.status,
       a.\`user\`,
       a.create_time,
       a.platform,
       a.s_name,
       a.seo_source,
       a.seo_keywords,
       a.ip,
       a.referrer,
       a.from_url,
       a.landing_page_url,
       a.url_title,
       a.to_peer,
       a.manual_time,
       a.begin_time,
       a.reply_msg_count,
       a.total_msg_count,
       a.msg_count,
       a.comment,
       a.finish_reason,
       a.finish_user,
       a.end_time,
       a.platform_description,
       a.browser_name,
       a.os_info,
       a.area,
       a.country,
       a.province,
       a.city,
       a.creator,
       a.name,
       a.idcard,
       a.phone,
       a.itcast_school_id,
       a.itcast_school,
       a.itcast_subject_id,
       a.itcast_subject,
       a.wechat,
       a.qq,
       a.email,
       a.gender,
       a.level,
       a.origin_type,
       a.information_way,
       a.working_years,
       a.technical_directions,
       a.customer_state,
       a.valid,
       a.anticipat_signup_date,
       a.clue_state,
       a.scrm_department_id,
       a.superior_url,
       a.superior_source,
       a.landing_url,
       a.landing_source,
       a.info_url,
       a.info_source,
       a.origin_channel,
       a.course_id,
       a.course_name,
       a.zhuge_session_id,
       a.is_repeat,
       a.tenant,
       a.activity_id,
       a.activity_name,
       a.follow_type,
       a.shunt_mode_id,
       a.shunt_employee_group_id,
       if(b.id is null and a.end_date <> '9999-99-99',a.end_date,date_sub(b.dt,1)) as end_date,
       a.start_date
from bxg_dwd.ft_customer_clue a
left join (select * from bxg_ods.customer_clue where dt='${TD_DATE}') b
on a.id = b.id;
insert overwrite table bxg_dwd.ft_customer_clue partition(start_date)
select 
*
from bxg_dwd.ft_customer_clue_tmp;

--============全量覆盖==========--
-- dt_customer客户信息表
insert overwrite table bxg_dwd.dt_customer partition (start_date)
select
id,
customer_relationship_id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
name,
idcard,
birth_year,
gender,
phone,
wechat,
qq,
email,
area,
leave_school_date,
graduation_date,
bxg_student_id,
creator,
origin_type,
origin_channel,
tenant,
md_id,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.customer ;

-- dt_customer_appeal 线索申诉表
insert overwrite table bxg_dwd.dt_customer_appeal partition (start_date)
select
id,
customer_relationship_first_id,
employee_id,
employee_name,
employee_department_id,
employee_tdepart_id,
appeal_status,
audit_id,
audit_name,
audit_department_id,
audit_department_name,
audit_date_time,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
tenant,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.customer_appeal;

-- dt_employee员工表
insert overwrite table bxg_dwd.dt_employee partition (start_date)
select
id,
email,
real_name,
phone,
department_id,
department_name,
remote_login,
job_number,
cross_school,
last_login_date,
creator,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
scrm_department_id,
leave_office,
leave_office_time,
reinstated_time,
superior_leaders_id,
tdepart_id,
tenant,
ems_user_name,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.employee;

-- dt_itcast_clazz班级信息表
insert overwrite table bxg_dwd.dt_itcast_clazz partition (dt)
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
itcast_brand,
clazz_type_state,
clazz_type_name,
teaching_mode,
start_time,
end_time,
comment,
detail,
uncertain,
tenant,
'${TD_DATE}' as dt
from
bxg_ods.itcast_clazz;

-- dt_itcast_school校区信息表
insert overwrite  table bxg_dwd.dt_itcast_school
select
id,
create_date_time,
update_date_time,
deleted,
name,
code,
tenant
from
bxg_ods.itcast_school;

-- dt_itcast_subject学科信息表
insert overwrite table bxg_dwd.dt_itcast_subject
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
name,
code,
tenant
from
bxg_ods.itcast_subject;

-- dt_scrm_department部门信息表
insert overwrite table bxg_dwd.dt_scrm_department partition (start_date)
select
id,
name,
parent_id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
id_path,
tdepart_code,
creator,
depart_level,
depart_sign,
depart_line,
depart_sort,
disable_flag,
tenant,
'9999-99-99'as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.scrm_department;"
